package com.orangelife.service.propFeeMeterRecord.impl;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.Resource;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Service;

import com.orangelife.exception.AppBizException;
import com.orangelife.service.Importlogs.QiniuUtils;
import com.orangelife.service.propFeeMeter.IPropFeeMeterService;
import com.orangelife.service.propFeeMeterRecord.IPropFeeMeterRecordService;
import com.orangelife.service.propFeeMeterRecord.model.PropFeeMeterRecordDto;
import com.orangelife.service.propFeeMeterRecordImportLog.IPropFeeMeterRecordImportLogService;
import com.orangelife.service.propFeeMeterRecordImportLog.model.PropFeeMeterRecordImportLogDto;
import com.orangelife.utils.IdUtil;
import com.orangelife.utils.ListSetUtils;
import com.orangelife.utils.PropertyUtils;
import com.orangelife.utils.StringUtils;

@Service("importRecord")
public class ImportRecordImpl implements IImportRecord {

	@Resource(name = "propFeeMeterRecordService")
	private IPropFeeMeterRecordService propFeeMeterRecordService;

	private QiniuUtils qiniuUtils = new QiniuUtils();

	@Resource(name = "propFeeMeterRecordImportLogService")
	private IPropFeeMeterRecordImportLogService propFeeMeterRecordImportLogService;

	@Resource(name = "propFeeMeterService")
	private IPropFeeMeterService propFeeMeterService;

	@Override
	public int importRecord(Sheet sheet, String commID, String fileName)
			throws AppBizException {
		PropFeeMeterRecordImportLogDto cmmImportLog = new PropFeeMeterRecordImportLogDto();
		Map<Integer, String> logmap = new TreeMap<Integer, String>();
		int rowCount = sheet.getPhysicalNumberOfRows();// 获得总行数
		if (rowCount < 1) {
			throw new AppBizException(2, "sheet页无数据");
		}

		/****
		 * 添加失败日志
		 */

		cmmImportLog.setCreateuserID("1");
		cmmImportLog.setImportLogID(IdUtil.getuuId());
		cmmImportLog.setItemStat("1");
		cmmImportLog.setStatus("1");
		cmmImportLog.setTscreate(new Date());
		cmmImportLog.setTsedit(new Date());
		cmmImportLog.setFileName(fileName);
		propFeeMeterRecordImportLogService
				.addPropFeeMeterRecordImportLog(cmmImportLog);

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("commID", commID);

		List<Map<String, Object>> meterInfo = PropertyUtils
				.turnToEntity(propFeeMeterService.getNoRecordPropFeeMeter(map,
						null, null));
		Row row = sheet.getRow(0);// 第一行
		int columnCount = row.getLastCellNum();// 获取列数
		Map<String, Integer> columnMap = new HashMap<String, Integer>();
		for (int i = 0; i < columnCount; i++) {
			String columnName = row.getCell((short) i).getStringCellValue()
					.trim();
			if (columnName.equals("表ID")) {
				columnMap.put("表ID", i);
			}

			if (columnName.equals("初始读数")) {
				columnMap.put("初始读数", i);
			}
			if (columnName.equals("初始使用量")) {
				columnMap.put("初始使用量", i);
			}
			if (columnName.equals("初始时间")) {
				columnMap.put("初始时间", i);
			}

			if (columnName.equals("楼栋")) {
				columnMap.put("楼栋", i);
			}
			if (columnName.equals("单元")) {
				columnMap.put("单元", i);
			}
			if (columnName.equals("房号")) {
				columnMap.put("房号", i);
			}
			if (columnName.equals("类型")) {
				columnMap.put("类型", i);
			}
		}
		try {
			checkRecordColum(columnMap, commID, cmmImportLog,logmap);
		} catch (Exception e) {
			return 0;
		}

		Set<PropFeeMeterRecordDto> recordDtos = notNull(sheet, rowCount,
				columnMap, commID, meterInfo, cmmImportLog,logmap);
		ListSetUtils listSetUtils = new ListSetUtils();
		List<List<PropFeeMeterRecordDto>> li = listSetUtils.splitList(
				listSetUtils.setToList(recordDtos), 200);
		for (int i = 0; i < li.size(); i++) {
			HandleRecordThread thread = new HandleRecordThread("线程[" + (i)
					+ "] ", li.get(i), propFeeMeterRecordService);
			thread.start();
		}
		return columnCount;

	}

	public Set<PropFeeMeterRecordDto> notNull(Sheet sheet, int rowCount,
			Map<String, Integer> columnMap, String commID,
			List<Map<String, Object>> meterInfo,
			PropFeeMeterRecordImportLogDto cmmImportLog,
			Map<Integer, String> logmap) throws AppBizException {
		int errortol = 0;
		int total = 0;
		Set<PropFeeMeterRecordDto> propFeeMeterRecordDtos = new HashSet<PropFeeMeterRecordDto>();
		Row firstRow = sheet.getRow(1);
		if (firstRow == null) {
			throw new AppBizException(99, "亲，请最少填写一行信息");
		}

		for (int i = 1; i < rowCount; i++) {
			try {
				PropFeeMeterRecordDto propFeeMeterRecordDto = new PropFeeMeterRecordDto();

				Row dataRow = sheet.getRow(i);
				if (dataRow == null) {
					break;
				}
				if (2 == (getCellInfoByHouseInfo(i + 1, dataRow, columnMap,
						logmap, meterInfo, propFeeMeterRecordDtos))) {
					errortol++;
					continue;
				} else if (1 == (getCellInfoByHouseInfo(i + 1, dataRow,
						columnMap, logmap, meterInfo, propFeeMeterRecordDtos))) {
					logmap.put(i + 1, "该行出现了的空行，导入停止");
					break;

				} else if (3 == (getCellInfoByHouseInfo(i + 1, dataRow,
						columnMap, logmap, meterInfo, propFeeMeterRecordDtos))) {
					// logmap.put(j + 1, "该行出现了的空行，导入停止");
					break;

				}

				/***
				 * 跳过空白行
				 */

				/*
				 * if
				 * (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
				 * .get("表ID"))))) { throw new AppBizException(2, "亲，第" + i +
				 * "行表ID为空"); }
				 */
				propFeeMeterRecordDto.setMeterID(this.getCellValue(dataRow
						.getCell(columnMap.get("表ID"))));

				/*
				 * if
				 * (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
				 * .get("初始读数"))))) { throw new AppBizException(2, "亲，第" + i +
				 * "行初始读数为空"); }
				 */

				propFeeMeterRecordDto.setRecord(this.getCellValue(dataRow
						.getCell(columnMap.get("初始读数"))));
				/*
				 * if (!StringUtils.isInt(propFeeMeterRecordDto.getRecord())) {
				 * throw new AppBizException(2, "第" + i + "行初始读数错误"); } if
				 * (StringUtils
				 * .isBlank(this.getCellValue(dataRow.getCell(columnMap
				 * .get("初始使用量"))))) { throw new AppBizException(2, "亲，第" + i +
				 * "行 初始使用量为空"); }
				 */

				propFeeMeterRecordDto.setUseAmount(this.getCellValue(dataRow
						.getCell(columnMap.get("初始使用量"))));
				/*
				 * if (!StringUtils.isInt(propFeeMeterRecordDto.getUseAmount()))
				 * { throw new AppBizException(2, "第" + i + "行初始使用量错误"); } if
				 * (StringUtils
				 * .isBlank(this.getCellValue(dataRow.getCell(columnMap
				 * .get("初始时间"))))) { throw new AppBizException(2, "亲，第" + i +
				 * "行初始时间为空"); }
				 */
				propFeeMeterRecordDto.setRowNum(String.valueOf(i));
				propFeeMeterRecordDto.setPeriod(this.getCellValue(dataRow
						.getCell(columnMap.get("初始时间"))));
				if (!this.getTimeDate(propFeeMeterRecordDto.getPeriod())) {
					throw new AppBizException(2, "亲，第" + i
							+ "行初始时间错误（正确格式 yyyy-mm）");
				}
				propFeeMeterRecordDto.setStatus("1");
				propFeeMeterRecordDtos.add(propFeeMeterRecordDto);
				total++;
			} catch (Exception e) {
				e.printStackTrace();
				logmap.put(i + 1, "行数据转换错误,错误原因：" + e.getMessage());
				errortol++;
			}
		}
		cmmImportLog.setStatus("2");
		cmmImportLog.setImportInfo("成功" + total + "条" + ",失败" + errortol + "条");
		String logurl = qiniuUtils.createErrorLogFile(logmap);
		cmmImportLog.setLink(logurl);

		cmmImportLog.setFinishTime(new Date(new Date().getTime() + 1000 * total
				* 4));
		cmmImportLog.setTsedit(new Date());
		cmmImportLog.setCommID(commID);
		propFeeMeterRecordImportLogService
				.editPropFeeMeterRecordImportLog(cmmImportLog);
		return propFeeMeterRecordDtos;

	}

	public void checkRecordColum(Map<String, Integer> map, String commID,
			PropFeeMeterRecordImportLogDto cmmImportLog,Map<Integer, String> logmap) throws AppBizException {
		try {
			if (map == null || map.size() <= 0) {
				throw new AppBizException(99, "亲,使用正确的excel的格式");
			}

			if (!map.containsKey("表ID")) {
				throw new AppBizException(99, "亲,使用正确的excel的格式(缺少表ID)");
			}

			if (!map.containsKey("楼栋")) {
				throw new AppBizException(99, "亲,使用正确的excel的格式(缺少楼栋)");
			}

			if (!map.containsKey("单元")) {
				throw new AppBizException(99, "亲,使用正确的excel的格式(缺少单元)");
			}
			if (!map.containsKey("房号")) {
				throw new AppBizException(99, "亲,使用正确的excel的格式(缺少房号)");
			}
			if (!map.containsKey("类型")) {
				throw new AppBizException(99, "亲,使用正确的excel的格式(缺少类型)");
			}
			if (!map.containsKey("初始读数")) {
				throw new AppBizException(99, "亲,使用正确的excel的格式(缺少初始读数)");
			}
			if (!map.containsKey("初始使用量")) {
				throw new AppBizException(99, "亲,使用正确的excel的格式(缺少初始使用量)");
			}
			if (!map.containsKey("初始时间")) {
				throw new AppBizException(99, "亲,使用正确的excel的格式(缺少初始时间)");
			}
		} catch (Exception e) {
			logmap.put(1, e.getMessage());
			/****
			 * 添加失败日志
			 */
			cmmImportLog.setStatus("3");
			cmmImportLog.setImportInfo("成功0条");
			String logurl = qiniuUtils.createErrorLogFile(logmap);
			cmmImportLog.setLink(logurl);
			cmmImportLog.setFinishTime(new Date());
			cmmImportLog.setTsedit(new Date());
			cmmImportLog.setCommID(commID);
			propFeeMeterRecordImportLogService
					.editPropFeeMeterRecordImportLog(cmmImportLog);
			throw new AppBizException(99, e.getMessage());
		}

	}

	public int getCellInfoByHouseInfo(int rownum, Row dataRow,
			Map<String, Integer> columnMap, Map<Integer, String> logmap,
			List<Map<String, Object>> meterInfo,
			Set<PropFeeMeterRecordDto> propFeeMeterRecordDtos)
			throws AppBizException {

		if (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
				.get("表ID"))))
				&& StringUtils.isBlank(this.getCellValue(dataRow
						.getCell(columnMap.get("初始读数"))))
				&& StringUtils.isBlank(this.getCellValue(dataRow
						.getCell(columnMap.get("楼栋"))))
				&& StringUtils.isBlank(this.getCellValue(dataRow
						.getCell(columnMap.get("单元"))))
				&& StringUtils.isBlank(this.getCellValue(dataRow
						.getCell(columnMap.get("房号"))))
				&& StringUtils.isBlank(this.getCellValue(dataRow
						.getCell(columnMap.get("类型"))))
				&& StringUtils.isBlank(this.getCellValue(dataRow
						.getCell(columnMap.get("初始使用量"))))

				&& StringUtils.isBlank(this.getCellValue(dataRow
						.getCell(columnMap.get("初始时间"))))) {
			return 1;

		}

		if (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
				.get("表ID"))))) {
			logmap.put(rownum, "亲，第" + rownum + "行表ID为空");
			return 2;

		}

		boolean isCheck = false;
		String meterID = this
				.getCellValue(dataRow.getCell(columnMap.get("表ID")));
		for (Map<String, Object> meter : meterInfo) {
			if (meterID.equals(meter.get("meterID").toString())) {
				isCheck = true;
			}
		}
		if (!isCheck) {
			logmap.put(rownum, "亲，第" + rownum + "行表ID不是该小区的信息");
			return 2;
		}
		if (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
				.get("初始读数"))))) {
			logmap.put(rownum, "亲，第" + rownum + "行初始读数为空");
			return 2;
		}
		if (!StringUtils.isInt(this.getCellValue(dataRow.getCell(columnMap
				.get("初始读数"))))) {
			logmap.put(rownum, "亲，第" + rownum + "行初始读数错误");
			return 2;
		}
		if (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
				.get("初始使用量"))))) {
			logmap.put(rownum, "亲，第" + rownum + "行初始使用量为空");
			return 2;
		}

		if (!StringUtils.isInt(this.getCellValue(dataRow.getCell(columnMap
				.get("初始使用量"))))) {
			logmap.put(rownum, "亲，第" + rownum + "行初始使用量格式错误");
			return 2;
		}

		if (StringUtils.isBlank(this.getCellValue(dataRow.getCell(columnMap
				.get("初始时间"))))) {
			logmap.put(rownum, "亲，第" + rownum + "行初始时间为空");
			return 2;
		}
		if (!this.getTimeDate(this.getCellValue(dataRow.getCell(columnMap
				.get("初始时间"))))) {
			logmap.put(rownum, "亲，第" + rownum + "行初始时间错误（正确格式 yyyy-mm）");
			return 2;
		}
		if (!this.getLimtDate(this.getCellValue(dataRow.getCell(columnMap
				.get("初始时间"))))) {
			logmap.put(rownum, "亲，第" + rownum + "行初始时间错误（请输入正确月份）");
			return 2;
		}

		if (propFeeMeterRecordDtos != null && propFeeMeterRecordDtos.size() > 0) {
			boolean isExIst = false;
			String row = "";
			for (PropFeeMeterRecordDto propFeeMeterRecordDto : propFeeMeterRecordDtos) {
				if (propFeeMeterRecordDto.getMeterID().equals(meterID)) {
					isExIst = true;
					row = propFeeMeterRecordDto.getRowNum();
					break;
				}
			}
			if (isExIst) {
				logmap.put(rownum, "亲，第" + rownum + "行相关信息与" + row + "行信息重复");
				return 2;
			}

		}

		return 0;
	}

	public String getCellValue(Cell cell) throws AppBizException {
		String cellValue = null;
		try {
			if (cell != null) {
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_NUMERIC: {
					if (DateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						SimpleDateFormat format = new SimpleDateFormat(
								"yyyy-MM-dd HH:mm:ss");
						cellValue = format.format(date);

					} else {
						DecimalFormat df = new DecimalFormat("#");
						cellValue = df.format(cell.getNumericCellValue());

					}
				}
					break;
				case Cell.CELL_TYPE_STRING: {
					cellValue = cell.getStringCellValue().trim();
				}
					break;

				default:
					break;
				}
			}
		} catch (Exception e) {
			throw new AppBizException(99, e.getMessage());
		}
		return cellValue;
	}

	public boolean getTimeDate(String date) {
		String eL = "[0-9]{4}-[0-9]{2}";
		Pattern p = Pattern.compile(eL);
		Matcher m = p.matcher(date);
		boolean dateFlag = m.matches();
		if (!dateFlag) {
			return false;
		}
		return true;
	}

	public boolean getLimtDate(String date) {
		if (date.length() < 7) {
			return false;
		}
		String month = date.substring(5, 7);
		if (Integer.parseInt(month) > 12 || Integer.parseInt(month) <= 0) {
			return false;
		}
		return true;
	}

}
